﻿using System;
using System.Linq;
using System.Linq.Expressions;

namespace FlashPay.DAO.Impl
{
    using FlashPay.DAO.Interface;
    using FlashPay.EF;
    using FlashPay.EF.Models;
    using FlashPay.Entity;
    using FlashPay.Entity.Enum;
    using FlashPay.Entity.Parameter;
    using FlashPay.Entity.Request.Payment;
    using FlashPay.Entity.Request.Transfer;
    using FlashPay.Entity.Response.Transfer;
    using FlashPay.ORM;
    using Microsoft.EntityFrameworkCore;
    using MySql.Data.MySqlClient;
    using System.Collections.Generic;
    using System.Data;

    /// <summary>
    /// 中转卡数据接口实现
    /// </summary>
    /// <remarks>2018-10-01 immi 创建</remarks>
    public class TransferDaoImpl :TransferDao
    {
        #region 注入
        /// <summary>
        /// EF上下文
        /// </summary>
        private FlashPayContext _context { set; get; }

        /// <summary>
        /// 注入
        /// </summary>
        /// <param name="context">EF上下文</param>
        public TransferDaoImpl(FlashPayContext context)
        {
            _context = context;
        }

        public void Dispose()
        {
            if (_context != null)
            {
                _context.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 获取订单月份
        /// </summary>
        /// <param name="month"></param>
        /// <returns></returns>
        private string GetOrderMonth(long orderNo)
        {
            return orderNo.ToString().Substring(0, 4);
        }

        /// <summary>
        /// 根据编号获取余额变化记录
        /// </summary>
        /// <param name="orderNo">编号</param>
        /// <returns>Company</returns>
        public TransportRecord Get(long orderNo)
        {
            var tableName = $"TransportRecord_{DateTime.Now.ToString("yyyy")}";

            var year = GetOrderMonth(orderNo);

            if (year != DateTime.Now.ToString("yyyy"))
            {
                tableName = $"TransportRecord_{year}";
            }

            var sql = $"SELECT * FROM {tableName} WHERE OrderNo={orderNo}";

            var transportRecord = _context.TransportRecord
                .FromSql(sql)
                .AsNoTracking()
                .SingleOrDefault();

            return transportRecord;
        }

        /// <summary>
        /// 获取中转卡
        /// </summary>
        /// <param name="query">查看参数</param>
        public DataGrid<TransferCardResponse> TransferCardPager(TransferCardQuery query)
        {
            var q = from bc in _context.BankCard
                    join
                    c in _context.Company on bc.CompanyId equals c.CompanyId
                    where query.CardType.Contains(bc.CardType) && bc.CompanyId > 0
                    select new
                    {
                        bc.Bcid,
                        c.CompanyId,
                        c.CompanyName,
                        c.CompanyNameEn,
                        bc.BankName,
                        bc.CardNumber,
                        bc.CardName,
                        bc.CardType,
                        bc.UsingStatus,
                        bc.EnableStatus,
                        bc.Balance,
                        bc.BalanceLastUpdate,
                        //TimeDifference
                        bc.TransportRate,
                        bc.IpAddress,
                        bc.Remark,
                        bc.VmClientId
                    };

            #region 条件
            //银行卡启用状态
            if (query.CompanyIds != null && query.CompanyIds.Any())
            {
                q = q.Where(c => query.CompanyIds.Contains(c.CompanyId));
            }
            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                q = q.Where(c => c.CompanyName.Contains(query.CompanyName));
            }
            //银行卡用戶名
            if (!string.IsNullOrEmpty(query.CardName))
            {
                q = q.Where(c => c.CardName.Contains(query.CardName));
            }
            //銀行卡号
            if (!string.IsNullOrEmpty(query.CardNumber))
            {
                q = q.Where(c => c.CardNumber.Contains(query.CardNumber));
            }
            //银行卡启用状态
            if (query.EnableStatus.HasValue)
            {
                q = q.Where(c => c.EnableStatus == query.EnableStatus.Value);
            }
            //更新时间排序
            if (query.LastUpdateDateSortType.HasValue)
            {
                if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.正常.GetHashCode())
                {
                    q = q.OrderByDescending(c => c.BalanceLastUpdate);
                }
                else if (query.LastUpdateDateSortType == (sbyte)LastUpdateDateSortType.不正常.GetHashCode())
                {
                    q = q.OrderBy(c => c.BalanceLastUpdate);
                }
            }
            #endregion

            var list = q.Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();
            var total = q.Count();

            var transferCardResponses = new List<TransferCardResponse>();

            list.ForEach(item => {

                var transferCardResponse = new TransferCardResponse()
                {
                    BcId = item.Bcid,
                    CompanyId = item.CompanyId,
                    CompanyName = item.CompanyName,
                    CompanyNameEn = item.CompanyNameEn,
                    BankName = item.BankName,
                    CardNumber = Util.WebUtil.SetCardNumber(item.CardNumber),
                    CardName = item.CardName,
                    CardType = item.CardType,
                    UsingStatus = item.UsingStatus,
                    EnableStatus = item.EnableStatus,
                    Balance = item.Balance.HasValue ? item.Balance.Value.ToString("#0.00") : null,
                    BalanceLastUpdate = item.BalanceLastUpdate.HasValue ? item.BalanceLastUpdate.Value.ToString("yyyy-MM-dd HH:mm:ss") : null,
                    TimeDifference = 0,
                    TransportRate = (item.TransportRate * 100).ToString("#0.00"),
                    IpAddress = item.IpAddress,
                    Remark = item.Remark,
                    VmClientId = item.VmClientId
                };

                if (item.BalanceLastUpdate.HasValue) {
                    var balanceLastUpdate = item.BalanceLastUpdate.Value;
                    var ts = DateTime.Now.Subtract(balanceLastUpdate).TotalMinutes;
                    if (ts > 0)
                    {
                        transferCardResponse.TimeDifference = ts;
                    }
                }

                transferCardResponses.Add(transferCardResponse);

            });

            //var footers = new List<Footer>();

            //footers.Add(new Footer() {
            //    Balance = "12.6"
            //});

            return new DataGrid<TransferCardResponse>()
            {
                Total = total,
                Rows = transferCardResponses,
                //Footer = footers,
                Success = true,
            };
        }

        /// <summary>
        /// 获取中转卡记录
        /// </summary>
        /// <param name="query">查看参数</param>
        public DataGrid<TransferCardRecordResponse> TransferCardRecordPager(TransferCardRecordQuery query)
        {
            var sql = string.Format("call sp_SelectTransportRecord('{0}', '{1}', '{2}');", query.StartTime, query.EndTime, string.Join(", ", query.CompanyIds));
            
            //公司列表
            var companys = _context.Company.ToList();
            //银行卡列表
            var bankCards = _context.BankCard.ToList();

            var q = _context.TransportRecord
                    .FromSql(sql)
                    .ToList();

            //公司名称
            if (!string.IsNullOrEmpty(query.CompanyName))
            {
                var companyIds = companys.Where(p => p.CompanyName.Contains(query.CompanyName)).Select(p=>p.CompanyId).ToList();
                q = q.Where(c => companyIds.Contains(c.CompanyId)).ToList() ;
            }

            if (query.OrderNo.HasValue)
            {
                q = q.Where(c => c.OrderNo == query.OrderNo.Value).ToList();
            }
            var list = q.Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).OrderByDescending(p => p.CreateDate).ToList();

            #region 模型转换
            var transferCardRecordResponses = new List<TransferCardRecordResponse>();

            list.ForEach(item => {

                #region 公司
                var companyName = "";
                var companyNameEn = "";
                if (companys != null && companys.Any())
                {
                    var company = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);
                    if (company != null)
                    {
                        companyName = company.CompanyName;
                        companyNameEn = company.CompanyNameEn;
                    }
                }
                #endregion

                #region 银行卡
                var bankName = "";
                var cardName = "";
                var cardNumber = "";
                if (bankCards != null && bankCards.Any())
                {
                    var bankCard = bankCards.FirstOrDefault(p => p.Bcid == item.TransportCardId);
                    if (bankCard != null)
                    {
                        bankName = bankCard.BankName;
                        cardName = bankCard.CardName;
                        cardNumber = bankCard.CardNumber;
                    }
                }
                #endregion

                var transferCardRecordResponse = new TransferCardRecordResponse();

                transferCardRecordResponse.OrderNo = item.OrderNo.ToString();
                transferCardRecordResponse.CompanyName = companyName;
                transferCardRecordResponse.CompanyNameEn = companyNameEn;
                transferCardRecordResponse.WithdrawalOrderNo = item.WithdrawalOrderNo;
                transferCardRecordResponse.BankName = bankName;
                transferCardRecordResponse.CardName = cardName;
                transferCardRecordResponse.CardNumber = Util.WebUtil.SetCardNumber(cardNumber);
                transferCardRecordResponse.WithdrawalAmount = item.WithdrawalAmount.ToString("#0.00");
                transferCardRecordResponse.TransportDate = item.TransportDate.HasValue ? item.TransportDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : null;
                transferCardRecordResponse.TransportFailInfo = string.IsNullOrEmpty(item.TransportFailInfo) ? null : item.TransportFailInfo;
                transferCardRecordResponse.TransportStatus = item.TransportStatus;
                transferCardRecordResponse.NoticeStatus = item.NoticeStatus;
                transferCardRecordResponse.NoticeLastDate = item.NoticeLastDate.HasValue ? item.NoticeLastDate.Value.ToString("yyyy-MM-dd HH:mm:ss") : null;
                transferCardRecordResponse.NoticeTimes = item.NoticeTimes;
                transferCardRecordResponse.WithdrawalAccountName = Util.WebUtil.Name(item.WithdrawalAccountName);
                transferCardRecordResponse.WithdrawalBankName = item.WithdrawalBankName;
                transferCardRecordResponse.WithdrawalCardNumber = Util.WebUtil.SetCardNumber(item.WithdrawalCardNumber);
                transferCardRecordResponse.CreateDate = item.CreateDate.ToString("yyyy-MM-dd HH:mm:ss");
                transferCardRecordResponse.ReadDate = item.ReadDate.HasValue? item.ReadDate.Value.ToString("yyyy-MM-dd HH:mm:ss"):"";
                transferCardRecordResponse.TransportFailInfo = item.TransportFailInfo;
                //(分钟)(当前时间-付款记录创建时间)>20   显示查询按钮
                var ts = DateTime.Now.Subtract(item.CreateDbdate).TotalMinutes;
                if (ts > 0)
                {
                    transferCardRecordResponse.TimeDifference = ts;
                }
                //(分钟)(当前时间-ReadDate)>15   显示重付付款
                if (item.ReadDate.HasValue) {
                    var tss = DateTime.Now.Subtract(item.ReadDate.Value).TotalMinutes;
                    if (tss > 0) {
                        transferCardRecordResponse.VmTimeDifference = tss;
                    }
                }

                if (item.TransportStatus == (sbyte)TransportStatus.付款中 && item.ReadDate.HasValue)
                {
                    var tsss = DateTime.Now.Subtract(item.ReadDate.Value).TotalMinutes;
                    if (tsss > 0)
                    {
                        transferCardRecordResponse.TransferDateTimeDifference = tsss;
                    }
                }
                else if (item.TransportDate.HasValue)
                {
                    var tsss = DateTime.Now.Subtract(item.TransportDate.Value).TotalMinutes;
                    if (tsss > 0)
                    {
                        transferCardRecordResponse.TransferDateTimeDifference = tsss;
                    }
                }
                transferCardRecordResponses.Add(transferCardRecordResponse);
            });
            #endregion

            return new DataGrid<TransferCardRecordResponse>()
            {
                Total = q.Count(),
                Rows = transferCardRecordResponses,
                Success = true,
            };
        }

        /// <summary>
        /// 转失败
        /// </summary>
        /// <returns></returns>
        public JResult TurnFailed(TurnFailedReqeust reqeust)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var tableName = $"TransportRecord_{DateTime.Now.ToString("yyyy")}";

                var year = GetOrderMonth(reqeust.OrderNo);

                if (year != DateTime.Now.ToString("yyyy"))
                {
                    tableName = $"TransportRecord_{year}";
                }

                var paymentRecord = this.Get(reqeust.OrderNo);
                if (paymentRecord != null)
                {
                    var confirmStatus = new List<sbyte>()
                    {
                        (sbyte)TransportConfirmStatus.自动审核通过,
                        (sbyte)TransportConfirmStatus.人工审核通过
                    };

                    var sql = $"update {tableName} set TransportStatus={(sbyte)TransportStatus.付款失败},TransportDate='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',TransportFailInfo='人工确认失败',NoticeLastDate='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',NoticeStatus={(sbyte)TransportNoticeStatus.未通知},NoticeTimes=1 where OrderNo={paymentRecord.OrderNo} and TransportStatus={(sbyte)TransportStatus.付款中} and ConfirmStatus in({string.Join(",", confirmStatus)}) and NoticeStatus={(sbyte)TransportNoticeStatus.未通知} and NoticeTimes=0";

                    var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                    if (updateStatusPaymentRecord <= 0)
                    {
                        throw new Exception("数据库操作失败！");
                    }

                    response.Success = true;
                    response.SuccessMessage = "操作成功.";
                }
                else
                {
                    throw new Exception("当前记录不存在！");
                }
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.ErrorMessage = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 转成功
        /// </summary>
        /// <returns></returns>
        public JResult TurnSuccess(TurnSuccessReqeust reqeust)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var tableName = $"TransportRecord_{DateTime.Now.ToString("yyyy")}";

                var year = GetOrderMonth(reqeust.OrderNo);

                if (year != DateTime.Now.ToString("yyyy"))
                {
                    tableName = $"TransportRecord_{year}";
                }

                var paymentRecord = this.Get(reqeust.OrderNo);
                if (paymentRecord != null)
                {
                    var sql = $"update {tableName} set TransportStatus={(sbyte)TransportStatus.付款成功},TransportDate='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',TransportFailInfo='人工确认成功',ReadDate=null,NoticeStatus={(sbyte)TransportNoticeStatus.未通知},NoticeTimes=0 where OrderNo={paymentRecord.OrderNo} and TransportStatus={(sbyte)TransportStatus.付款中} and ConfirmStatus in({(sbyte)TransportConfirmStatus.自动审核通过},{(sbyte)TransportConfirmStatus.人工审核通过.GetHashCode()}) and NoticeStatus={(sbyte)TransportNoticeStatus.未通知} and NoticeTimes=0";

                    var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                    if (updateStatusPaymentRecord <= 0)
                    {
                        throw new Exception("数据库操作失败！");
                    }

                    response.Success = true;
                    response.SuccessMessage = "操作成功.";
                }
                else
                {
                    throw new Exception("当前记录不存在！");
                }
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.ErrorMessage = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 重置
        /// </summary>
        /// <returns></returns>
        public JResult Reset(ResetReqeust reqeust)
        {
            var response = new JResult()
            {
                Success = false
            };

            try
            {
                var tableName = $"TransportRecord_{DateTime.Now.ToString("yyyy")}";

                var year = GetOrderMonth(reqeust.OrderNo);

                if (year != DateTime.Now.ToString("yyyy"))
                {
                    tableName = $"TransportRecord_{year}";
                }

                var transportRecord = this.Get(reqeust.OrderNo);
                if (transportRecord != null)
                {
                    var sql = $"update {tableName} set NoticeStatus={(sbyte)TransportNoticeStatus.通知失败},NoticeTimes=1 where OrderNo={reqeust.OrderNo} and TransportStatus={(sbyte)TransportStatus.付款成功} and NoticeStatus={(sbyte)TransportNoticeStatus.通知失败} and NoticeTimes=5";

                    var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                    if (updateStatusPaymentRecord <= 0)
                    {
                        throw new Exception("该中转记录已发生改变.");
                    }

                    response.Success = true;
                    response.SuccessMessage = "操作成功.";
                }
                else
                {
                    throw new Exception("当前记录不存在！");
                }
            }
            catch (Exception ex)
            {
                response.Success = false;
                response.ErrorMessage = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 重新付款
        /// </summary>
        /// <param name="request"></param>
        public JResult Repayment(RepaymentRequest request)
        {
            var response = new JResult() {
                Success = false
            };

            try
            {
                var tableName = $"TransportRecord_{DateTime.Now.ToString("yyyy")}";

                var year = GetOrderMonth(request.OrderNo);

                if (year != DateTime.Now.ToString("yyyy"))
                {
                    tableName = $"TransportRecord_{year}";
                }

                var transportRecord = this.Get(request.OrderNo);
                if (transportRecord == null)
                {
                    throw new Exception("中转记录不存在！");
                }

                if (transportRecord.TransportStatus != (sbyte)TransportStatus.付款中) {
                    throw new Exception("操作失败：中转记录状态只能为付款中！");
                }

                var transportNoticeStatus = new List<sbyte>()
                {
                    (sbyte)TransportNoticeStatus.未通知.GetHashCode(),
                };

                if (!transportNoticeStatus.Contains(transportRecord.NoticeStatus))
                {
                    throw new Exception("操作失败：通知状态只能未通知");
                }

                //ET @cmd = concat("update ", TABLENAME, " set FeeRatio = null, FeeTotal = null, PaymentCardId = null, CardNumber = null, AllocatedTime = null, PaymentStatus = 1, PaymentDate = null, PaymentFailInfo = null where NoticeStatus = 1 and NoticeLastDate is null and NoticeTimes = 0 and ReadDate is null and PaymentStatus = 4 and PaymentType = 7 and RecordRealOrderNo is nulland OrderNo = ", _OrderNo);
                var sql = $"update {tableName} set FeeRatio=null,FeeTotal=null,TransportCardID=null,CardNumber=null,TransportStatus={(sbyte)TransportStatus.未付款},TransportDate=null,TransportFailInfo=null where OrderNo={request.OrderNo} and TransportStatus={(sbyte)TransportStatus.付款中} and NoticeStatus= 1 and NoticeTimes=0 and ReadDate is null and NoticeLastDate is null";

                var updateStatusPaymentRecord = _context.Database.ExecuteSqlCommand(sql);
                if (updateStatusPaymentRecord <= 0)
                {
                    throw new Exception("该中转记录已发生改变！");
                }

                response.Success = true;
                response.SuccessMessage = "操作成功.";
            }
            catch (Exception ex) {
                response.ErrorMessage = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 检查订单是否可以重新付款
        /// </summary>
        /// <param name="orderNo">订单号</param>
        /// <returns></returns>
        public JResult<TransportRecordResponse> VerifyRepayment(long orderNo)
        {

            var result = new JResult<TransportRecordResponse>()
            {
                Success = false
            };

            try
            {
                var tableName = $"TransportRecord_{DateTime.Now.ToString("MM")}";

                var year = GetOrderMonth(orderNo);

                if (year != DateTime.Now.ToString("MM"))
                {
                    tableName = $"TransportRecord_{year}";
                }

                var sql = $"SELECT * FROM {tableName} WHERE TransportStatus=4 and NoticeStatus=1 and NoticeTimes=0 and NoticeLastDate is null and OrderNo={orderNo}";

                var paymentRecord = _context.TransportRecord
                        .FromSql(sql)
                        .AsNoTracking()
                        .SingleOrDefault();

                if (paymentRecord != null)
                {
                    var tlyPaymentRecordResponse = new TransportRecordResponse()
                    {
                        OrderNo = orderNo.ToString(),
                        IsRenewPayment = true
                    };

                    if (paymentRecord.ReadDate.HasValue)
                    {
                        var tsss = DateTime.Now.Subtract(paymentRecord.ReadDate.Value).TotalMinutes;
                        if (tsss > 0)
                        {
                            tlyPaymentRecordResponse.TransportDateTimeDifference = tsss;
                        }
                    }

                    result.Data = tlyPaymentRecordResponse;
                }
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.ErrorMessage = ex.Message;
            }
            return result;
        }
    }
}
